package com.apes.scm.account.account.repository;

import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * Created by lishikun on 2020/11/26 19:57
 */
@Component
public class InventoryBatchDao extends ApesDao {
    public List<Map> queryInventoryAge(JSONObject param) {


        String sSQL = "SELECT G.* " +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE " +
                "           FROM (" +

                "                            " +
                InventoryAgeSQL(param) +

                ") G " +
                "          WHERE ROWNUM <=  (:PAGE + 1) * :SIZE) G " +
                " WHERE RN >= :PAGE * :SIZE + 1";

        return find(sSQL, param);

    }

    public String InventoryAgeSQL(JSONObject param) {
        String countTypeNameSQL = "";
        if ("supplier".equals(param.getString("countType"))) {
            countTypeNameSQL = "(select c.name from mas_party_role a,mas_party c where b.supplier_id = a.id and a.PARTY_ID = c.id) supplier_name, ";
        } else if ("company".equals(param.getString("countType"))) {
            countTypeNameSQL = "(select c.name from mas_party_role a,mas_party c where a.id = b.company_ID and a.PARTY_ID = c.id) COMPANY_NAME, ";
        } else if ("classify".equals(param.getString("countType"))) {
            countTypeNameSQL = "(select a.name from mas_classify a where b.classify_id = a.id) classify_name, ";
        } else if ("product".equals(param.getString("countType"))) {
            countTypeNameSQL = "(select c.name from mas_product c where b.product_id = c.id) PRODUCT_NAME, ";
        } else if ("brand".equals(param.getString("countType"))) {
            countTypeNameSQL = "(select a.name from mas_brand a where b.brand_id = a.id) brand_name, ";
        }
        ;
        String sSQL =
                "     select G.*,MAX(ROWNUM) OVER() TOTAL from (                       " +
                        "       select " +
                        "b." + param.getString("countType") + "_id," +
                        countTypeNameSQL +
//                "b.supplier_name" +

                        " " +
                        "       sum(b.SL_15)  SL_15,       " +
                        "       sum(b.tax_cost_price * b.SL_15)    SL_15_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_15)    SL_15_no_tax_price, " +
                        "       sum(b.SL_30)  SL_30,       sum(b.tax_cost_price * b.SL_30)    SL_30_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_30)    SL_30_no_tax_price, " +
                        "       sum(b.SL_60)  SL_60,       sum(b.tax_cost_price * b.SL_60)    SL_60_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_60)    SL_60_no_tax_price, " +
                        "       sum(B.SL_90)  SL_90,       sum(b.tax_cost_price * b.SL_90)    SL_90_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_90)    SL_90_no_tax_price, " +
                        "       sum(b.SL_180) SL_180,      sum(b.tax_cost_price * b.SL_180)   SL_180_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_180)    SL_180_no_tax_price, " +
                        "       sum(b.SL_365)  SL_365,     sum(b.tax_cost_price * b.SL_365)   SL_365_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_365)    SL_365_no_tax_price, " +
                        "       sum(B.SL_365UP)  SL_365UP, sum(b.tax_cost_price * b.SL_365UP) SL_365UP_tax_price, " +
                        "       sum(b.no_tax_cost_price * b.SL_365UP)    SL_365UP_no_tax_price, " +
                        "       sum( b.SL_15 + b.SL_30 + b.SL_60 + b.SL_90 + b.SL_180 + b.SL_365 + b.SL_365UP) count_balance_qty, " +
                        "       sum((b.SL_15 + b.SL_30 + b.SL_60 + b.SL_90 + b.SL_180 + b.SL_365 + b.SL_365UP) * b.tax_cost_price) count_tax_price, " +
                        "       sum((b.SL_15 + b.SL_30 + b.SL_60 + b.SL_90 + b.SL_180 + b.SL_365 + b.SL_365UP) * b.no_tax_cost_price) count_no_tax_price " +
                        " from " +
                        "(SELECT a.company_id, a.tax_cost_price,a.no_tax_cost_price,a.supplier_id,a.product_id, " +
                        "       (select b.name from mas_classify b, mas_product c where a.product_id = c.id and c.classify_id = b.id) classify_name, " +
                        "       (select b.id from mas_classify b, mas_product c where a.product_id = c.id and c.classify_id = b.id) classify_id, " +
                        "       (select b.name from mas_brand b, mas_product c where a.product_id = c.id and c.brand_id = b.id) brand_name, " +
                        "       (select b.id from mas_brand b, mas_product c where a.product_id = c.id and c.brand_id = b.id) brand_id, " +
                        "       (select c.name from mas_party_role b,mas_party c where a.supplier_id = b.id and b.PARTY_ID = c.id) supplier_name, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE AND A.purchase_in_date >= SYSDATE-15 THEN A.balance_qty ELSE 0 END) SL_15, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-15 AND A.purchase_in_date >= SYSDATE-30 THEN A.balance_qty ELSE 0 END) SL_30, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-30 AND A.purchase_in_date >= SYSDATE-60 THEN A.balance_qty ELSE 0 END) SL_60, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-60 AND A.purchase_in_date >= SYSDATE-90 THEN A.balance_qty ELSE 0 END) SL_90, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-90 AND A.purchase_in_date  >= SYSDATE-180 THEN A.balance_qty ELSE 0 END) SL_180, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-180 AND A.purchase_in_date >= SYSDATE-365 THEN A.balance_qty ELSE 0 END) SL_365, " +
                        "       (CASE WHEN A.purchase_in_date < SYSDATE-365 THEN A.balance_qty ELSE 0 END ) SL_365UP " +
                        "  FROM acc_inventory_batch A ) b " +

                        "  group by " +
                        "b." + param.getString("countType") + "_id" +
                        "                 ) G";
        return sSQL;
    }

    public List<Map> countInventoryAge(JSONObject param) {
        String sSQL = "select count(1) as count from (" + InventoryAgeSQL(param) + ") G";
        return find(sSQL, param);
    }


    /**
     * String --> Date
     *
     * @param endDate
     * @param dateFormat
     * @return
     */
    public Date StringToDate(String endDate, String dateFormat) {
        DateFormat format = new SimpleDateFormat(dateFormat);
        Date eDate = null;
        try {
            eDate = format.parse(endDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return eDate;
    }

    public List<Map> findBalance(JSONObject param, String company, String orderField, String orderType,int page, int size) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( " +
                " SELECT to_char(a.create_date, 'yyyy-mm-dd') create_date, " +
                "       '[' || a.product_id || ']' || " +
                "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name, " +
                "       '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' || " +
                "        (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b " +
                "          WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY_NAME, " +
                "       case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' " +
                "       end COOPERATION, sum(a.BALANCE_QTY) BALANCE_QTY, " +
                "       (SELECT b.tax_cost_price " +
                "           FROM acc_inventory_batch b where a.inventory_id = b.id) priceUnit, " +
                "       (sum(a.BALANCE_QTY) * (SELECT b.tax_cost_price " +
                "       FROM acc_inventory_batch b where a.inventory_id = b.id)) amount, " +
                "       to_char(a.purchase_in_date, 'yyyy-mm-dd') purchase_in_date, " +
                "       TRUNC(SYSDATE - A.purchase_in_date) reservoir, " +
                "       (SELECT z.name FROM MAS_PRODUCT M, acc_tax z " +
                "          WHERE m.tax_id = z.id and a.PRODUCT_ID = M.ID) tax, " +
                "       '[' || A.COMPANY_ID || ']' || " +
                "        (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "          WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "       (SELECT NAME FROM MAS_PRODUCT_UOM WHERE ID = A.PRODUCT_UOM_ID) PRODUCT_UOM_NAME " +
                "  FROM ACC_INVENTORY_BATCH_DAY a " +
                " WHERE a.BALANCE_QTY > 0 " +
                "   and (SELECT z.qty_mgmt FROM MAS_PRODUCT M, mas_product_group z " +
                "         WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) = 1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" AND a.CREATE_DATE >= to_date(substr(:CREATE_DATE_DATE,1,10),'yyyy-MM-dd') AND a.CREATE_DATE <= to_date(substr(:CREATE_DATE,1,10),'yyyy-MM-dd')");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID = :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M, mas_classify b  WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) = :CLASSIFY_NAME");
        }
        if (param.get("COOPERATION_ID") != null) {
            stringBuffer.append(" AND a.COOPERATION_ID = :COOPERATION_ID");
        }
        if (param.get("PURCHASE_IN_DATE") != null) {
            stringBuffer.append(" AND a.purchase_in_date >= to_date(substr(:PURCHASE_IN_DATE_DATE,1,10),'yyyy-MM-dd') AND a.purchase_in_date <= to_date(substr(:PURCHASE_IN_DATE,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in(:COMPANY_ID)");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        stringBuffer.append(" group by a.create_date,a.product_id, a.COOPERATION_ID,a.inventory_id,a.purchase_in_date,A.COMPANY_ID,A.PRODUCT_UOM_ID");
        if (StringUtils.isEmpty(orderField)) {
            stringBuffer.append(" order by a.create_date ");
        } else {
            stringBuffer.append(" order by " + orderField + " ");
        }

        if (StringUtils.isEmpty(orderType)) {
            stringBuffer.append("desc");
        } else {
            stringBuffer.append(orderType);
        }
        stringBuffer.append(" ) CB WHERE ROWNUM <="+ (page == 0? size : (page +1)* size)+" )T_CB WHERE T_CB.rowno >"+((page+1)-1) * size);
        return this.find(stringBuffer.toString(), param);
    }

    public Object findBalanceCount(JSONObject param, String company) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append( " SELECT count(1) count" +
                "  FROM ACC_INVENTORY_BATCH_DAY a " +
                " WHERE a.BALANCE_QTY > 0 " +
                "   and (SELECT z.qty_mgmt FROM MAS_PRODUCT M, mas_product_group z " +
                "         WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) = 1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" AND a.CREATE_DATE >= to_date(substr(:CREATE_DATE_DATE,1,10),'yyyy-MM-dd') AND a.CREATE_DATE <= to_date(substr(:CREATE_DATE,1,10),'yyyy-MM-dd')");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID = :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M, mas_classify b  WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) = :CLASSIFY_NAME");
        }
        if (param.get("COOPERATION_ID") != null) {
            stringBuffer.append(" AND a.COOPERATION_ID = :COOPERATION_ID");
        }
        if (param.get("PURCHASE_IN_DATE") != null) {
            stringBuffer.append(" AND a.purchase_in_date >= to_date(substr(:PURCHASE_IN_DATE_DATE,1,10),'yyyy-MM-dd') AND a.purchase_in_date <= to_date(substr(:PURCHASE_IN_DATE,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in(:COMPANY_ID)");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    public List<Map> findBalanceMonth(JSONObject param, String company,String orderField, String orderType,int page, int size) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( " +
                "SELECT  substr(a.month,1,4)|| '-' || substr(a.month,5,2) create_date, " +
                "       '[' || a.product_id || ']' || " +
                "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name, " +
                "       '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' || " +
                "        (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b " +
                "          WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY_NAME, " +
                "       case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' " +
                "       end COOPERATION, sum(a.BALANCE_QTY) BALANCE_QTY, " +
                "       (SELECT b.tax_cost_price " +
                "           FROM acc_inventory_batch b where a.inventory_id = b.id) priceUnit, " +
                "       (sum(a.BALANCE_QTY) * (SELECT b.tax_cost_price " +
                "       FROM acc_inventory_batch b where a.inventory_id = b.id)) amount, " +
                "       to_char(a.purchase_in_date, 'yyyy-mm-dd') purchase_in_date, " +
                "       TRUNC(SYSDATE - A.purchase_in_date) reservoir, " +
                "       (SELECT z.name FROM MAS_PRODUCT M, acc_tax z " +
                "          WHERE m.tax_id = z.id and a.PRODUCT_ID = M.ID) tax, " +
                "       '[' || A.COMPANY_ID || ']' || " +
                "        (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "          WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "       (SELECT NAME FROM MAS_PRODUCT_UOM WHERE ID = A.PRODUCT_UOM_ID) PRODUCT_UOM_NAME FROM ACC_INVENTORY_BATCH_MONTH  a " +
                "        WHERE  a.BALANCE_QTY > 0 " +
                "        and (SELECT z.qty_mgmt FROM MAS_PRODUCT M ,mas_product_group z WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) =1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" and a.MONTH  >= CONCAT(substr(:CREATE_DATE_DATE,1,4),substr(:CREATE_DATE_DATE,6,2)) and a.MONTH  <= CONCAT(substr(:CREATE_DATE,1,4),substr(:CREATE_DATE,6,2)) ");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID in :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M, mas_classify b  WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID)  in :CLASSIFY_NAME");
        }
        if (param.get("COOPERATION_ID") != null) {
            stringBuffer.append(" AND a.COOPERATION_ID = :COOPERATION_ID");
        }
        if (param.get("PURCHASE_IN_DATE") != null) {
            stringBuffer.append(" AND a.purchase_in_date >= to_date(substr(:PURCHASE_IN_DATE_DATE,1,10),'yyyy-MM-dd') AND a.purchase_in_date < to_date(substr(:PURCHASE_IN_DATE,1,10),'yyyy-MM-dd')+1");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in :COMPANY_ID");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        stringBuffer.append(" group by a.MONTH,a.product_id, a.COOPERATION_ID,a.inventory_id,a.purchase_in_date,A.COMPANY_ID,A.PRODUCT_UOM_ID ");
        if (StringUtils.isEmpty(orderField)) {
            stringBuffer.append(" order by a.month ");
        } else {
            stringBuffer.append(" order by " + orderField + " ");
        }

        if (StringUtils.isEmpty(orderType)) {
            stringBuffer.append("desc");
        } else {
            stringBuffer.append(orderType);
        }
        stringBuffer.append(" ) CB WHERE ROWNUM <="+ (page == 0? size : (page +1)* size)+" )T_CB WHERE T_CB.rowno >"+((page+1)-1) * size);
        return this.find(stringBuffer.toString(), param);
    }

    public Object findBalanceMonthCount(JSONObject param, String company) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(
                "SELECT  count(1) count FROM ACC_INVENTORY_BATCH_MONTH  a " +
                "        WHERE  a.BALANCE_QTY > 0 " +
                "        and (SELECT z.qty_mgmt FROM MAS_PRODUCT M ,mas_product_group z WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) =1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" and a.MONTH  >= CONCAT(substr(:CREATE_DATE_DATE,1,4),substr(:CREATE_DATE_DATE,6,2)) and a.MONTH  <= CONCAT(substr(:CREATE_DATE,1,4),substr(:CREATE_DATE,6,2)) ");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID in :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M, mas_classify b  WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID)  in :CLASSIFY_NAME");
        }
        if (param.get("COOPERATION_ID") != null) {
            stringBuffer.append(" AND a.COOPERATION_ID = :COOPERATION_ID");
        }
        if (param.get("PURCHASE_IN_DATE") != null) {
            stringBuffer.append(" AND a.purchase_in_date >= to_date(substr(:PURCHASE_IN_DATE_DATE,1,10),'yyyy-MM-dd') AND a.purchase_in_date < to_date(substr(:PURCHASE_IN_DATE,1,10),'yyyy-MM-dd')+1");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in :COMPANY_ID");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    public List<Map> queryDaily(JSONObject param, String company,String orderField, String orderType) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT (SELECT M.CLASSIFY_ID FROM MAS_PRODUCT M WHERE A.PRODUCT_ID = M.ID) CLASSIFY_ID, " +
                "        (SELECT B.NAME FROM MAS_PRODUCT M,mas_classify b WHERE b.id=m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY_NAME, " +
                "        a.product_id , (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS PRODUCT_NAME, " +
                "        to_char(a.create_date,'yyyy-mm-dd')create_date ,to_char(a.create_date-1, 'yyyy-mm-dd') create_date1,a.cooperation_id,  " +
                "        (SELECT b.name FROM acc_cooperation b where a.cooperation_id=b.id) cooperation,sum(a.last_balance_qty) last_balance_qty,sum(a.last_balance_amt) last_balance_amt, " +
                "        sum(a.PURCHASE_QTY) PURCHASE_QTY,sum(a.purchase_amt) purchase_amt,sum(a.purchase_return_qty) purchase_return_qty,sum(a.purchase_return_amt) purchase_return_amt, " +
                "        sum(a.tran_qty) tran_qty,sum(a.tran_amt) tran_amt,sum(a.sale_qty) sale_qty,sum(a.sale_amt) sale_amt, " +
                "        sum(a.price_change_qty) price_change_qty,sum(a.price_change_amt) price_change_amt,sum(a.balance_qty) balance_qty, " +
                "        sum(a.balance_amt) balance_amt,sum(a.PROFIT_AND_LOSS_QTY) PROFIT_AND_LOSS_QTY,sum(a.profit_and_loss_amt) profit_and_loss_amt, " +
                "        A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "        case when (sum(a.last_balance_qty) + sum(a.PURCHASE_QTY) - sum(a.purchase_return_qty) + " +
                "        sum(a.profit_and_loss_qty) + sum(a.tran_qty) - sum(a.sale_qty)) != sum(a.balance_qty) then 1 " +
                "        else 0 end errorChecking,'1' BELONG " +
                "        FROM ACC_INVENTORY_DAY a where  (SELECT z.qty_mgmt FROM MAS_PRODUCT M ,mas_product_group z WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) =1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" AND a.CREATE_DATE >= to_date(substr(:CREATE_DATE_DATE,1,10),'yyyy-MM-dd') AND a.CREATE_DATE <= to_date(substr(:CREATE_DATE,1,10),'yyyy-MM-dd')");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID in :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M, mas_classify b  WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID)  in :CLASSIFY_NAME");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in :COMPANY_ID");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        stringBuffer.append(" group by a.PRODUCT_ID, A.COMPANY_ID,a.create_date, a.cooperation_id ");
        if (StringUtils.isEmpty(orderField)) {
            stringBuffer.append(" order by a.create_date ");
        } else {
            stringBuffer.append(" order by " + orderField + " ");
        }

        if (StringUtils.isEmpty(orderType)) {
            stringBuffer.append("desc");
        } else {
            stringBuffer.append(orderType);
        }
        return this.find(stringBuffer.toString(), param);
    }

    public List<Map> queryDailyMonth(JSONObject param, String company,String orderField, String orderType) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("  SELECT " +
                "        (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) CLASSIFY_ID, " +
                "        (SELECT b.NAME FROM MAS_PRODUCT M,mas_classify b WHERE b.id=m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY_NAME, " +
                "        a.product_id , (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS PRODUCT_NAME, " +
                "         substr(a.month, 1, 4) || '-' || substr(a.month, 5, 2) create_date, " +
                "       case " +
                "         when substr(a.month, 5, 2) = 1 then " +
                "          substr(a.month, 1, 4)-1 || '-' || 12 " +
                "         else " +
                "          substr(a.month, 1, 4) || '-' || substr(a.month, 5, 2)  " +
                "       end create_date1,a.cooperation_id,  " +
                "        (SELECT b.name FROM acc_cooperation b where a.cooperation_id=b.id) cooperation,sum(a.last_balance_qty) last_balance_qty,sum(a.last_balance_amt) last_balance_amt, " +
                "        sum(a.PURCHASE_QTY) PURCHASE_QTY,sum(a.purchase_amt) purchase_amt,sum(a.purchase_return_qty) purchase_return_qty,sum(a.purchase_return_amt) purchase_return_amt, " +
                "        sum(a.tran_qty) tran_qty,sum(a.tran_amt) tran_amt,sum(a.sale_qty) sale_qty,sum(a.SALE_COST_AMT) sale_amt, " +
                "        sum(a.price_change_qty) price_change_qty,sum(a.price_change_amt) price_change_amt,sum(a.balance_qty) balance_qty, " +
                "        sum(a.balance_amt) balance_amt,sum(a.PROFIT_AND_LOSS_QTY) PROFIT_AND_LOSS_QTY,sum(a.profit_and_loss_amt) profit_and_loss_amt, " +
                "        A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "        case when (sum(a.last_balance_qty) + sum(a.PURCHASE_QTY) - sum(a.purchase_return_qty) + " +
                "        sum(a.profit_and_loss_qty) + sum(a.tran_qty) - sum(a.sale_qty)) != sum(a.balance_qty) then " +
                "        1 else 0 end errorChecking ,'2' BELONG FROM ACC_INVENTORY_MONTH a where 1=1  " +
                "        and (SELECT z.qty_mgmt FROM MAS_PRODUCT M ,mas_product_group z WHERE a.PRODUCT_ID = M.ID and z.id = m.product_group_id) =1");
        if (param.get("CREATE_DATE") != null) {
            stringBuffer.append(" and a.MONTH  >= CONCAT(substr(:CREATE_DATE_DATE,1,4),substr(:CREATE_DATE_DATE,6,2)) and a.MONTH  <= CONCAT(substr(:CREATE_DATE,1,4),substr(:CREATE_DATE,6,2)) ");
        }
        if (param.get("PRODUCT_ID") != null) {
            stringBuffer.append(" AND a.PRODUCT_ID in :PRODUCT_ID");
        }
        if (param.get("CLASSIFY_NAME") != null) {
            stringBuffer.append(" AND (SELECT b.id FROM MAS_PRODUCT M,mas_classify b WHERE b.id=m.classify_id and a.PRODUCT_ID = M.ID)  in :CLASSIFY_NAME");
        }
        if (param.get("COMPANY_ID") != null) {
            stringBuffer.append(" AND a.COMPANY_ID in :COMPANY_ID");
        }
        if (!"A0000".equals(company)) {
            stringBuffer.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.COMPANY_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        stringBuffer.append(" group by a.PRODUCT_ID, A.COMPANY_ID,a.month, a.cooperation_id ");
        if (StringUtils.isEmpty(orderField)) {
            stringBuffer.append(" order by a.month ");
        } else {
            stringBuffer.append(" order by " + orderField + " ");
        }

        if (StringUtils.isEmpty(orderType)) {
            stringBuffer.append("desc");
        } else {
            stringBuffer.append(orderType);
        }
        return this.find(stringBuffer.toString(), param);
    }
}
